package com.carleasoft.mps.data.mybatisplus.initsql;

import com.carleasoft.mps.data.prefix.PropUtils;
import com.carleasoft.mps.data.prefix.YmlUtils;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;

/**
 * <p>Description: 服务安装升级脚本</p>
 *
 * @author haoyi
 * @since 2020/7/24
 */
//@Order(100000)
//@Async
//@Component(value = "sqlInstallUpgrade")
public class SqlInstallUpgrade implements CommandLineRunner {

    public Logger log = LoggerFactory.getLogger(SqlInstallUpgrade.class);

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Override
    public void run(String... args) {
        // 执行更新安装数据库
        this.install(dataSourceProperties);
        // 执行完以后销毁这个 Bean
    }

    /**
     * 启动服务初始化升级或安装sql脚本
     */
    public void install(DataSourceProperties ds) {
        //查询版本信息
        String version = this.versionInfo(ds);

        if (version.equals("notExist")) {
            insDB(ds);
            insTableData(ds);
        } else if (version.equals("0.0")) {
            insTableData(ds);
        } else {
            updateTableData(ds, version);
        }
    }

    /**
     * 初始化库
     */
    public void insDB(DataSourceProperties ds) {
        log.info("初始化安装数据库和版本信息表");
        // 初次安装
        List<String> results = new ArrayList<>();
        String[] list = null;
        try {
            URL dbUpgradeUrl = SqlInstallUpgrade.class.getResource("/sql/db");
            if (dbUpgradeUrl != null) {
                String basePath = dbUpgradeUrl.getPath();
                basePath = URLDecoder.decode(basePath, "utf-8");
                list = new File(basePath).list();
                if (list != null && list.length > 0) {
                    for (String s : list) {
                        results.add("/sql/db/" + s);
                    }
                }
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        this.executeSqlFile(results, ds);
    }

    /**
     * 初始化表
     */
    public void insTableData(DataSourceProperties ds) {
        log.info("初始化安装sql");
        // 初次安装
        List<String> results = new ArrayList<>();
        String[] list = null;
        try {
            URL dbUpgradeUrl = SqlInstallUpgrade.class.getResource("/sql/ins-table-data");
            if (dbUpgradeUrl != null) {
                String basePath = dbUpgradeUrl.getPath();
                basePath = URLDecoder.decode(basePath, "utf-8");
                list = new File(basePath).list();
                if (list != null && list.length > 0) {
                    for (String s : list) {
                        results.add("/sql/ins-table-data/" + s);
                    }
                }
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        this.executeSqlFile(results, ds);
    }

    /**
     * 更新表和数据
     */
    public void updateTableData(DataSourceProperties ds, String version) {
        log.info("进行升级验证");
        //升级服务
        try {
            String[] list = null;

            URL dbUpgradeUrl = SqlInstallUpgrade.class.getResource("/sql/upgrade");
            if (dbUpgradeUrl != null) {
                String basePath = dbUpgradeUrl.getPath();
                basePath = URLDecoder.decode(basePath, "utf-8");
                list = new File(basePath).list();

                List<Double> versionNumbers = new ArrayList<Double>();
                if (list != null && list.length > 0) {
                    for (String fileName : list) {
                        if (fileName.contains(".sql")) {
                            String nameSplit = fileName.replace(".sql", "");
                            if (nameSplit.contains("sql/upgrade") && nameSplit.contains("-")) {
                                String versionNumber = nameSplit.split("-")[1];

                                if (Double.parseDouble(versionNumber) > Double.parseDouble(version)) {
                                    versionNumbers.add(Double.valueOf(versionNumber));
                                }
                            }
                        }
                    }
                }

                if (!CollectionUtils.isEmpty(versionNumbers)) {
                    log.info("升级sql脚本" + versionNumbers.toString());
                    Collections.sort(versionNumbers);
                    List<String> results = new ArrayList<String>();
                    for (Double num : versionNumbers) {
                        results.add("/sql/upgrade/upgrade-" + num + ".sql");
                    }
                    this.executeSqlFile(results, ds);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取当前版本信息
     */
    private String versionInfo(DataSourceProperties db) {
        String servername = this.getProp("spring.application.name");
        String version = "0.0";
        Connection con = null;
        Statement statement = null;
        ResultSet rs = null;

        try {
            con = this.getConnection(db);
            statement = con.createStatement();
            String sql = "select VERSION FROM system_info  where  SERVER_NAME = '" + servername + "'";
            rs = (ResultSet) statement.executeQuery(sql);
            while (rs.next()) {
                version = rs.getString("VERSION");
            }
        } catch (Exception e) {
            version = "notExist";
            log.error("查询版本信息异常！", e);
        } finally {
            this.close(rs);
            this.close(statement);
            this.close(con);
        }
        return version;
    }

    /**
     * 写入文件
     */
    public void executeSqlFile(List<String> sqlFileNames, DataSourceProperties db) {
        // 获取连接对象
        Connection conn = this.getConnection(db);
        try {
            // 设置不自动提交
            conn.setAutoCommit(false);
            // 初始化ScriptRunner
            ScriptRunner runner = new ScriptRunner(conn);
            // 设置不自动提交
            runner.setAutoCommit(false);
            // true，遇见错误会停止执行，打印并抛出异常，捕捉异常，并进行回滚，保证在一个事务内执行；
            // false，遇见错误不会停止，会继续执行，会打印异常信息，并不会抛出异常，当前方法无法捕捉异常无法进行回滚操作，无法保证在一个事务内执行；
            runner.setStopOnError(true);
            // true则获取整个脚本并执行；
            // false则按照自定义的分隔符每行执行；
            runner.setSendFullScript(false);
            // 定义命令间的分隔符
            runner.setDelimiter(";");
            runner.setFullLineDelimiter(false);
            // 设置是否输出日志，null不输出日志，不设置自动将日志输出到控制台
            runner.setLogWriter(null);

            // 读取文件
            for (String sqlFileName : sqlFileNames) {
                InputStream inputStream = null;

                Resource resource = new ClassPathResource(sqlFileName);
                inputStream = resource.getInputStream();

                // 如果有多个sql文件，可以写多个runner.runScript(xxx),
                runner.runScript(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
            }
            // 执行
            conn.commit();
        } catch (Exception e) {
            log.error("执行sql文件异常！", e);
            try {
                conn.rollback();
            } catch (Exception ex) {
                log.error("关闭数据库连接异常", ex);
            }
        } finally {
            close(conn);
        }
    }

    /**
     * 获取连接
     */
    private Connection getConnection(DataSourceProperties ds) {
        String driver = ds.getDriverClassName();
        String dbUser = ds.getUsername();
        String dbPassword = ds.getPassword();

        //声明一个连接对象
        Connection conn = null;
        try {
            //注册驱动
            Class.forName(driver);
            //获得连接对象
            conn = DriverManager.getConnection(ds.getUrl(), dbUser, dbPassword);
        } catch (ClassNotFoundException e) {
            //捕获驱动类无法找到异常
            log.error("捕获驱动类无法找到异常", e);
        } catch (SQLException e) {
            log.error("捕获sql异常", e);
        }
        return conn;
    }

    /**
     * 关闭连接
     */
    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭预处理对象
     */
    public void close(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭预处理对象
     */
    public void close(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭结果集对象
     */
    public void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取配置文件信息
     */
    private String getYml(String key) {
        String result = null;
        Map props = null;
        try {

            props = YmlUtils.getYmlByFileName("application.yml");

            result = (String) props.get(key);
        } catch (Exception e) {
            log.error("获取配置文件信息异常", e);
        }
        return result;
    }

    /**
     * 获取配置文件信息
     */
    private String getProp(String key) {
        String result = null;
        Properties props = PropUtils.getProByFileName();

        result = props.getProperty(key);

        return result;
    }
}
